﻿



CREATE PROC [dbo].[usp_PerformanceHourly_Build]
AS

INSERT INTO [dbo].[PerformanceHourly] (RoleRowId, PerformanceCounterRowId, InstanceNumber, DateTimeUTC, MinValue, MaxValue, AverageValue, StdDev)
SELECT A.RoleRowId, A.PerformanceCounterRowId, A.InstanceNumber, A.DateTimeUTC, A.MinValue, A.MaxValue, A.AverageValue, IsNull(A.StdDev, 0) AS StdDev
FROM [vw_PerformanceSample_Hourly] A
LEFT OUTER JOIN 
		(
			-- Get the latest
			SELECT MAX(A.DateTimeUTC) As DateTimeUTC, A.RoleRowId, A.InstanceNumber, A.PerformanceCounterRowId
			FROM [vw_PerformanceSample_Hourly] A
			GROUP BY A.RoleRowId, A.InstanceNumber, A.PerformanceCounterRowId
		) AS B
		ON A.DateTimeUTC = B.DateTimeUTC
			AND A.RoleRowId = B.RoleRowId
			AND A.InstanceNumber = B.InstanceNumber
			AND A.PerformanceCounterRowId = B.PerformanceCounterRowId
	LEFT OUTER JOIN PerformanceHourly C
		ON A.DateTimeUTC = C.DateTimeUTC
			AND A.RoleRowId = C.RoleRowId
			AND A.InstanceNumber = C.InstanceNumber
			AND A.PerformanceCounterRowId = C.PerformanceCounterRowId
WHERE B.DateTimeUTC is null
	AND C.PerformanceHourlyRowId is null

